<!DOCTYPE html>
<html lang="zh-cn">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>openGauss Blog  | openGauss与postgresql日常使用差异</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">

    
    <link rel="shortcut icon" href="https://xzx666.gitee.io/lookeng/img/favicon.ico" type="image/x-icon" />
    <link rel="apple-touch-icon" href="https://xzx666.gitee.io/lookeng/img/apple-touch-icon.png" />

    <link rel="stylesheet" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

    
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
    
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/commen.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/blog.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/mobile.css?t=1607593672000" />
    <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/pagination.css?t=1607593672000">
    
    <script>
      var _hmt = _hmt || [];
      (function() {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?34e9f585f29581007941aa1698181871";
        var s = document.getElementsByTagName("script")[0]; 
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
</head>
<body>
  <script src="//cdn1.lncld.net/static/js/3.0.4/av-min.js"></script>
  <script src='//unpkg.com/valine/dist/Valine.min.js'></script>






<div class="container">
    <div class="columns blog-detail">
        <div class="post_detail">
            <div class="is-child box">
                <div class="breadCrumb"><a href="\zh\">博客/</a></div>
                <div class="blog-detail-header">
                    <h1>openGauss与postgresql日常使用差异</h1>
                    <div class="blog-detail-prop">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-date.png">
                        <span class="article_right_date">2020-11-17</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-author.png">
                        <span class="article_right_author">高云龙</span>
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-view.png">
                        <span id="/zh/post/gaoyunlong/opengauss%E4%B8%8Epostgresql%E6%97%A5%E5%B8%B8%E4%BD%BF%E7%94%A8%E5%B7%AE%E5%BC%82/" class="leancloud_visitors" data-flag-title="openGauss与postgresql日常使用差异">
                            <i class="leancloud-visitors-count"></i>                     
                        </span>
                    </div>
                    <div class="blog-detail-tags">
                        <img src="https://xzx666.gitee.io/lookeng/img/icon-tag.png">
                        
                        <a href="https://xzx666.gitee.io/lookeng/zh/tags/opengauss%E4%B8%8Epostgresql%E6%97%A5%E5%B8%B8%E4%BD%BF%E7%94%A8%E5%B7%AE%E5%BC%82">openGauss与postgresql日常使用差异</a>
                        
                    </div>
                </div>
                <div class="content blog-content">
                    

<h1 id="opengauss与postgresql日常使用差异-a-name-zh-cn-topic-0291959506-a">openGauss与postgresql日常使用差异<a name="ZH-CN_TOPIC_0291959506"></a></h1>

<h2 id="密码加密-a-name-section6186205418215-a">密码加密<a name="section6186205418215"></a></h2>

<ul>
<li><p>postgresql默认密码加密方式是md5。</p></li>

<li><p>openGauss默认密码加密方式是sha256。</p></li>

<li><p>使用navicate、pgadmin3等客户端开发工具访问og，需要修改加密方式。</p></li>

<li><p>如果在本地用用户名密码登陆数据库没问题。</p></li>

<li><p>但是用其他工具连接数据库报用户或密码错误。</p></li>

<li><p>可能是密码加密方式不对，需要看pg_hba.conf 及 参数。</p>

<pre><code>password_encryption_type = 0		#Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only
</code></pre></li>
</ul>

<h2 id="字符串存储-a-name-section1177545618615-a">字符串存储<a name="section1177545618615"></a></h2>

<p>在postgresql里，char(n)、varchar(n) n代表是字符；最多存储1GB。</p>

<p>在openGauss里，char(n)、varcahr(n) n代表的是字节，nvarchar2(n) n代表是字符；最多存储100MB。</p>

<pre><code>---
---postgresql字符测试
---
postgres=# \d dt
                       Table &quot;public.dt&quot;
 Column |         Type         | Collation | Nullable | Default
--------+----------------------+-----------+----------+---------
 id     | integer              |           |          |
 col1   | character varying(8) |           |          |

postgres=# insert into dt values(3,'中文字符长度测试');
INSERT 0 1
postgres=# insert into dt values(4,'yingwen8');
INSERT 0 1
postgres=# insert into dt values(4,'yingwen88');
ERROR:  value too long for type character varying(8)

---
---openGauss字符测试
---
mydb=# \d+ dt
                                 Table &quot;public.dt&quot;
 Column |         Type         | Modifiers | Storage  | Stats target | Description
--------+----------------------+-----------+----------+--------------+-------------
 id     | integer              |           | plain    |              |
 col1   | character varying(8) |           | extended |              |
 col2   | nvarchar2(8)         |           | extended |              |
Has OIDs: no
Options: orientation=row, compression=no

mydb=# insert into dt(id,col1) values(3,'yingwen8');
INSERT 0 1
mydb=# insert into dt(id,col1) values(3,'yingwen88');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1
mydb=# insert into dt(id,col1) values(3,'中文测试');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1
mydb=# insert into dt(id,col1) values(3,'中文测');
ERROR:  value too long for type character varying(8)
CONTEXT:  referenced column: col1

mydb=# insert into dt(id,col2) values(4,'中文字符长度测试');
INSERT 0 1
mydb=# insert into dt(id,col2) values(4,'yingwen8');
INSERT 0 1
mydb=# insert into dt(id,col2) values(4,'yingwen88');
ERROR:  value too long for type nvarchar2(8)
CONTEXT:  referenced column: col2
mydb=#
</code></pre>

<h2 id="null-与-空字符-a-name-section81541742111210-a">null 与 空字符<a name="section81541742111210"></a></h2>

<ul>
<li><p>在postgresql里 null != ‘’</p></li>

<li><p>在openGauss里‘’转换成 null，没有‘’</p></li>
</ul>

<p>&mdash;-+&mdash;&mdash;</p>

<pre><code>---
---postgresql测试
---
postgres=# create table dt(id int,col1 varchar(8));
CREATE TABLE
postgres=# insert into dt values(1,null);
INSERT 0 1
postgres=# insert into dt values(2,'');
INSERT 0 1
postgres=# select * from dt;
 id | col1
----+------
  1 |
  2 |
(2 rows)

postgres=# select * from dt where col1 is null;
 id | col1
----+------
  1 |
(1 row)

postgres=# select * from dt where col1='';
 id | col1
----+------
  2 |
(1 row)

postgres=#
---
---openGauss测试
---
mydb=# create table dt(id int,col1 varchar(8));
CREATE TABLE
mydb=# insert into dt values(1,null);
INSERT 0 1
mydb=# insert into dt values(1,'');
INSERT 0 1
mydb=# select * from dt;
 id | col1
----+------
  1 |
  1 |
(2 rows)

mydb=# select * from dt where col1 is null;
 id | col1
----+------
  1 |
  1 |
(2 rows)

mydb=# select * from dt where col1='';
 id | col1
----+------
(0 rows)

mydb=#
</code></pre>

                </div>
            </div>
            <div class="box" style="margin-top:15px;">
                <div style="font-size:14px;color:gray"><strong>【免责声明】</strong>本文仅代表作者本人观点，与本网站无关。本网站对文中陈述、观点判断保持中立，不对所包含内容的准确性、可靠性或完整性提供任何明示或暗示的保证。本文仅供读者参考，由此产生的所有法律责任均由读者本人承担。</div>
            </div>
            <div class="post-comment">
                
                  
  <link rel="stylesheet" href="https://xzx666.gitee.io/lookeng/css/custom.css" />
  <div id="vcomments" lang="zh-cn" mess="说点什么吧..."></div>

  <script type="text/javascript">
    const placeholder = document.getElementById("vcomments").getAttribute("mess"),
          lang = document.getElementById("vcomments").getAttribute("lang");
    const  langs = lang == "zh-cn" ? "zh-cn" : "en";
    new Valine({
        el: '#vcomments' ,
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm', 
        placeholder: placeholder,
        visitor: true,
		    meta: ['nick','mail','link'],
        lang: langs
    });
  </script>
            </div>
        </div>
    </div>
</div>
<script>
    $(function (){
        var query = new AV.Query('Counter');
        query.equalTo('url', decodeURIComponent('\/zh\/post\/gaoyunlong\/opengauss%E4%B8%8Epostgresql%E6%97%A5%E5%B8%B8%E4%BD%BF%E7%94%A8%E5%B7%AE%E5%BC%82\/'));
        query.find().then(function (data) {
            if(data.length && (data[0].attributes.author === 'openGauss')){
                var counter = AV.Object.createWithoutData('Counter', data[0].id);
                counter.set('author', '高云龙');
                counter.save();
            }
        })
    })
</script>







<input id="iframeUrl" type="text" style="display: none;" value=https://xzx666.gitee.io/>




<script defer src="https://xzx666.gitee.io/lookengjs/all.js"></script>
<script src="https://xzx666.gitee.io/lookengjs/flexible.js"></script>
<script>
  const hash = window.location.search,
        pageurl = window.location.href;
  var langss = document.querySelector("html").lang === "zh-cn" ? "zh" : "en";
  console.log("hash:",hash,"pageurl:",pageurl,"ttr",pageurl.split(langss + "/")[1])

  if(!document.getElementById("vcomments")) {
    new Valine({
        appId: '6wfgavgIRqmpC3hjHqQVtFWF-gzGzoHsz',
        appKey: 'QRqrBDBB0p0YhrGe9IJ169ip',
        avatar:'mm',
        visitor: true,
		    meta: ['nick','mail','link']
    });
  }
 
  
    


    const url = document.getElementById("iframeUrl").value;

    function observe (el, options, callback) {
      var MutationObserver = window.MutationObserver || window.WebKitMutationObserver || window.MozMutationObserver
      var observer = new MutationObserver(callback)
      observer.observe(el, options)
    }

    var options = {
        childList: true,
        subtree: true,
        characterData: true
      }
    observe(document.body, options, (records, instance) => {
    const height = document.body.scrollHeight;
    parent.postMessage(height,url);
    })
    if(pageurl.split(langss + "/")[1]) parent.postMessage(pageurl.split(langss + "/")[1],url);
    

    if(document.querySelector("#notFound")) parent.postMessage("我404了",url);

</script>
</body>
</html>
